create database SIMS
go
use SIMS
go
create table Users
(
	UserID varchar(10) primary key,
	Username varchar(30) not null,
	Password varchar(32) not null,
	UserType smallint not null,
	[Name] nvarchar(50) not null,
	Age smallint not null,
	Address nvarchar(100) not null,
	Phone varchar(15),
	Email varchar(50)
)
go
create table Categories
(
	CategoryID varchar(10) primary key,
	[Name] nvarchar(50) not null
)
go
create table Items
(
	ItemID varchar(20) primary key,
	[Name] nvarchar(50) not null,
	IDCategory varchar(10) references Categories(CategoryID),
	Price float not null,
	Unit varchar(10) not null,
	Quantity int not null,
	Stock int not null,
	Producer nvarchar(50),
	ImportedDate datetime,
	ExpiredDate datetime,
	Description text
)
go
create table Sales
(
	SalesID varchar(10) primary key,
	IDUser varchar(10) references Users(UserID),
	CustomerName nvarchar(50),
	CustomerAddress nvarchar(100),
	OrderDate datetime not null,
	Status smallint not null,
	Note text
)
go
create table SalesDetails
(
	SalesDetailsID varchar(20) primary key,
	IDSales varchar(10) references Sales(SalesID),
	IDItem varchar(20) references Items(ItemID),
	Price float not null,
	Quantity int not null,
	Status smallint not null,
	Note text
)
go
create table Reports
(
	ReportID varchar(10) primary key,
	IDUserFrom varchar(10) references Users(UserID),
	IDUserTo varchar(10) references Users(UserID),
	Status smallint not null,
	Description text not null
)


/* Add users */
insert into Users 
values ('AD001', 'admin', 'fcea920f7412b5da7be0cf42b8c93759', 0, 'Huu NC', 20, 'Ha Noi', '0123456789', 'huu@sims.com')
insert into Users 
values ('SP001', 'salesperson', 'fcea920f7412b5da7be0cf42b8c93759', 1, 'Thanh Trung', 22, 'Ha Noi', '0123987654', 'thanhtrung@sims.com')
insert into Users 
values ('SM001', 'salesmanager', 'fcea920f7412b5da7be0cf42b8c93759', 2, 'Chi Huu', 20, 'Ha Noi', '0123487650', 'chihuu@sims.com')
insert into Users 
values ('IM001', 'inventorymanager', 'fcea920f7412b5da7be0cf42b8c93759', 3, 'Thanh Quang', 22, 'Ha Noi', '0125436787', 'thanhquang@sims.com')

/* Add items and categories */
insert into Categories
values ('C001', 'Fruits')
insert into Categories
values ('C002', 'Vegetables')
insert into Categories
values ('C003', 'Spices')
insert into Categories
values ('C004', 'CDs and DVDs')
insert into Categories
values ('C005', 'Books')

insert into Items
values ('I0001', 'Apple', 'C001', 2.45, 'kg', 120, 120, null, '2011-4-12', null, null)
insert into Items
values ('I0002', 'Orange', 'C001', 2.05, 'kg', 320, 400, null, '2011-4-12', null, null)
insert into Items
values ('I0003', 'Pear', 'C001', 4.6, 'kg', 0, 100, null, '2011-4-11', null, null)

insert into Items
values ('I0004', 'Carrot', 'C002', 1.45, 'kg', 200, 220, null, '2011-4-10', null, null)
insert into Items
values ('I0005', 'Spinach', 'C002', 3.2, 'kg', 320, 400, null, '2011-4-12', null, null)
insert into Items
values ('I0006', 'Corn', 'C002', 5.7, 'kg', 0, 0, null, null, null, null)

/* Sales */
insert into sales
values ('S0001', 'SP001', 'Someone', 'Somewhere', '2011-4-11', 1, null)
insert into sales
values ('S0002', 'SP001', 'Someone', 'Somewhere', '2011-4-12', 0, null)
insert into sales
values ('S0003', 'SP001', 'Someone', 'Somewhere', '2011-4-12', 2, null)
insert into sales
values ('S0004', 'SP001', 'Someone', 'Somewhere', '2011-4-13', 3, null)

/* Sales details */
insert into SalesDetails
values ('SD00001', 'S0001', 'I0001', 2.4, 30, 1, null)
insert into SalesDetails
values ('SD00002', 'S0001', 'I0002', 2.05, 50, 1, null)
insert into SalesDetails
values ('SD00003', 'S0001', 'I0003', 4.6, 20, 1, null)

insert into SalesDetails
values ('SD00004', 'S0002', 'I0003', 4.6, 10, 0, null)
insert into SalesDetails
values ('SD00005', 'S0002', 'I0005', 3.2, 20, 0, null)
insert into SalesDetails
values ('SD00006', 'S0002', 'I0006', 5.7, 15, 0, null)

insert into SalesDetails
values ('SD00007', 'S0003', 'I0004', 1.45, 10, 1, null)
insert into SalesDetails
values ('SD00008', 'S0003', 'I0005', 3.2, 20, 1, null)

insert into SalesDetails
values ('SD00009', 'S0004', 'I0003', 4.6, 18, 0, null)

/* Reports */
insert into Reports
values ('R0001', 'SP001', 'SM001', 0, 'Report here to sales manager')
insert into Reports
values ('R0002', 'SP001', 'IM001', 0, 'Report here to inventory manager')